﻿/////////////////////////////////////////////////////////////////////////////////
//
// InstantCube
// - Automatically generates Analysis Services cubes
// (C) Copyright Johan Åhlén 2009
// 
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// any later version.
// 
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
// See the GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see http://www.gnu.org/licenses/.
//
// More info on InstantCube website: http://instantcube.codeplex.com
//
/////////////////////////////////////////////////////////////////////////////////


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AdvantIQ.InstantCube.Model;
using Microsoft.AnalysisServices;

namespace AdvantIQ.InstantCube.Emitters
{
    public class SSASEmitter : IDisposable
    {
        public Project Project { get; set; }

        private Server server;
        private string dbName;

        public SSASEmitter(Project project)
        {
            this.Project = project;
            server = new Server();
            server.Connect(Project.Connections.SSASConnection);
            dbName = Project.Namings.SsasDbName;
        }

        public void EmitDatabase()
        {
            if (server.Databases.ContainsName(dbName))
            {
                server.Databases[dbName].Drop();
                server.Databases.Remove(dbName);
                server.Update();
            }
            var database = server.Databases.Add(dbName);
            // Add lines here to set database properties like Collation
            database.Update();
        }

        public void EmitDataSource()
        {
            var database = server.Databases[dbName];
            var datasource = new RelationalDataSource(dbName, dbName);
            datasource.ConnectionString = Project.Connections.OLEDBConnection + "Initial Catalog=" + 
                Project.Namings.SqlDbName;
            database.DataSources.Add(datasource);
            datasource.Update();
        }

        public void EmitDSV()
        {
            var database = server.Databases[dbName];
            var dsv = new DataSourceView(dbName);
            database.DataSourceViews.Add(dsv);
            dsv.Schema = new DataSet();

            foreach (var dimension in Project.Dimensions.Values)
            {
                EmitDSVTable(dsv, dimension.GetTableName(), dimension.GetFields(), dimension.GetPrimaryKey());
            }

            foreach (var cube in Project.Cubes)
            {
                foreach (var mg in cube.MeasureGroups)
                {
                    var tbl = EmitDSVTable(dsv, mg.GetTableName(), mg.GetFields(), mg.GetPrimaryKey());

                    foreach (var fk in mg.GetForeignKeys())
                    {
                        var col1 = dsv.Schema.Tables[fk.RemoteTable].Columns[fk.RemoteField];
                        var col2 = tbl.Columns[fk.LocalField];
                        var relation = new DataRelation(fk.ID, col1, col2);
                        dsv.Schema.Relations.Add(relation);
                    }
                }
            }

            dsv.DataSourceID = dbName;
            dsv.Update();
        }

        public void EmitDimensions()
        {
            var database = server.Databases[dbName];

            foreach (var modelDimension in Project.Dimensions.Values)
            {
                var dimension = new Microsoft.AnalysisServices.Dimension(modelDimension.ID);
                dimension.Description = modelDimension.ID;
                dimension.Source = new DataSourceViewBinding(dbName);
                if (modelDimension is TimeDimension)
                    dimension.Type = DimensionType.Time;
                else
                {
                    dimension.Type = DimensionType.Regular;
                    EmitSurrogateKeyAttribute(modelDimension, dimension);
                }

                foreach (var attribute in modelDimension.Attributes.Values)
                    EmitAttribute(modelDimension, dimension, attribute);

                foreach (var hierarchy in modelDimension.Hierarchies)
                    EmitHierarchy(dimension, hierarchy, modelDimension);

                database.Dimensions.Add(dimension);
                dimension.Update(UpdateOptions.ExpandFull);
            }
        }

        public void EmitCubes()
        {
            var database = server.Databases[dbName];

            foreach (var modelCube in Project.Cubes)
            {
                var cube = database.Cubes.Add(modelCube.ID);
                cube.Source = new DataSourceViewBinding(dbName);
                cube.StorageMode = StorageMode.Molap;
                cube.DefaultMeasure = "";

                foreach (var modelMeasureGroup in modelCube.MeasureGroups)
                {
                    var measureGroup = cube.MeasureGroups.Add(modelMeasureGroup.ID);
                    var partition = measureGroup.Partitions.Add(modelMeasureGroup.ID);
                    partition.StorageMode = StorageMode.Molap;

                    foreach (var measure in modelMeasureGroup.Measures)
                        EmitMeasure(modelMeasureGroup, measureGroup, measure);

                    foreach (var cubeDim in modelMeasureGroup.Dimensions)
                        EmitCubeDimension(modelCube, modelMeasureGroup, cube, measureGroup, cubeDim);
                }

                var script = EmitMDX(modelCube);
                var command = new Command(script.ToString());
                cube.MdxScripts.Add("MDX").Commands.Add(command);

                EmitActions(modelCube, cube);
                EmitKPIs(modelCube, cube);

                cube.Update(UpdateOptions.ExpandFull);
            }
        }

        private void EmitActions(Model.Cube modelCube, Microsoft.AnalysisServices.Cube olapCube)
        {
            foreach (var modelAction in modelCube.CubeActions)
            {
                var action = new StandardAction(modelAction.ID, modelAction.ID);
                action.Caption = modelAction.Caption;
                action.Expression = modelAction.Expr;
                action.Condition = modelAction.Condition;
                action.Target = modelAction.Target;
                action.TargetType = (ActionTargetType)Enum.Parse(typeof(ActionTargetType), modelAction.TargetType, true);
                action.Type = (ActionType)Enum.Parse(typeof(ActionType), modelAction.ActionType, true);
                olapCube.Actions.Add(action);
            }
        }

        private void EmitKPIs(Model.Cube modelCube, Microsoft.AnalysisServices.Cube cube)
        {
            foreach (var kpi in modelCube.KPIs)
            {
                var olapKpi = new Kpi(kpi.ID, kpi.ID);
                olapKpi.Value = kpi.ValueExpr;
                olapKpi.Goal = kpi.GoalExpr;
                olapKpi.Status = kpi.StatusExpr;
                olapKpi.Trend = kpi.TrendExpr;
                cube.Kpis.Add(olapKpi);
            }
        }

        private string EmitMDX(AdvantIQ.InstantCube.Model.Cube cube)
        {
            var script = new StringBuilder();
            script.Append("CALCULATE;\r\n\r\n");

            foreach (var calculatedMember in cube.CalculatedMembers)
            {
                script.Append("\r\nCREATE MEMBER CURRENTCUBE.");
                script.Append(calculatedMember.ID);
                script.Append(" AS \r\n");
                script.Append(calculatedMember.Expr);
                if (!string.IsNullOrEmpty(calculatedMember.FormatString))
                {
                    script.Append(", FORMAT_STRING = \"");
                    script.Append(calculatedMember.FormatString);
                    script.Append("\"");
                }
                script.Append(", VISIBLE = ");
                script.Append(calculatedMember.Visible ? "1" : "0");
                script.Append(";\r\n");
            }

            foreach (var measureGroup in cube.MeasureGroups)
            {
                foreach (var cubeDim in measureGroup.Dimensions)
                {
                    var dim = Project.Dimensions[cubeDim.ID];
                    if (!string.IsNullOrEmpty(dim.DefaultMember))
                    {
                        script.Append("\r\nALTER CUBE CURRENTCUBE UPDATE DIMENSION ");
                        script.Append(cubeDim.ID);
                        script.Append(", DEFAULT_MEMBER = ");
                        script.Append(dim.DefaultMember);
                        script.Append(";\r\n");
                    }
                }
            }

            foreach (var calculatedCell in cube.CalculatedCells)
            {
                script.Append("\r\nSCOPE (");
                script.Append(calculatedCell.ID);
                script.Append(");\r\n");
                script.Append("\tTHIS = ");
                script.Append(calculatedCell.Expr);
                script.Append(";\r\n");
                if (!string.IsNullOrEmpty(calculatedCell.FormatString))
                {
                    script.Append("FORMAT_STRING(THIS) = \"");
                    script.Append(calculatedCell.FormatString);
                    script.Append("\";\r\n");
                }
                script.Append("END SCOPE\r\n");
            }

            return script.ToString();
        }

        private DataTable EmitDSVTable(DataSourceView dsv, string tableName, IEnumerable<FieldInfo> fields, string primaryKey)
        {
            var tbl = new DataTable();
            tbl.TableName = tableName;
            foreach (var field in fields)
            {
                tbl.Columns.Add(new DataColumn(field.ID, DetermineType(field.Type)));
            }
            tbl.ExtendedProperties.Add("TableType", "Table");
            tbl.ExtendedProperties.Add("FriendlyName", tbl.TableName);
            tbl.ExtendedProperties.Add("DbSchemaName", "dbo");
            tbl.ExtendedProperties.Add("DbTableName", tbl.TableName);
            tbl.Constraints.Add("FK_" + tableName, tbl.Columns[primaryKey], true);
            dsv.Schema.Tables.Add(tbl);

            return tbl;
        }

        private void EmitMeasure(CubeMeasureGroup measureGroup, MeasureGroup olapMeasureGroup, CubeMeasure measure)
        {
            var olapMeasure = olapMeasureGroup.Measures.Add(measure.ID);
            olapMeasure.Source = new DataItem(measureGroup.GetTableName(), measure.ID);
            olapMeasure.AggregateFunction = (AggregationFunction)Enum.Parse(typeof(AggregationFunction), measure.AggregationFunction, true);
            olapMeasure.DataType = MeasureDataType.Inherited;
            olapMeasure.Source.DataType = DetermineOleDbType(measure.Type);
        }

        private void EmitCubeDimension(AdvantIQ.InstantCube.Model.Cube cube, CubeMeasureGroup measureGroup, Microsoft.AnalysisServices.Cube olapCube, MeasureGroup olapMeasureGroup, AdvantIQ.InstantCube.Model.CubeDimension cubeDim)
        {
            MeasureGroupDimension mgd = null;

            if (cubeDim.RefType == AdvantIQ.InstantCube.Model.CubeDimension.DimensionRefType.Regular)
            {
                string rmgdid;
                string keycol;

                if (cubeDim.Alias != null && cubeDim.Alias.Length > 0)
                {
                    if (!olapCube.Dimensions.ContainsName(cubeDim.Alias))
                        olapCube.Dimensions.Add(cubeDim.ID, cubeDim.Alias, cubeDim.Alias);
                    rmgdid = cubeDim.Alias;
                    keycol = cubeDim.Alias;
                }
                else
                {
                    if (!olapCube.Dimensions.ContainsName(cubeDim.ID))
                        olapCube.Dimensions.Add(cubeDim.ID);
                    rmgdid = cubeDim.ID;
                    keycol = Project.Dimensions[cubeDim.ID].GetPrimaryKey();
                }

                mgd = new RegularMeasureGroupDimension(rmgdid);
                var mga = ((RegularMeasureGroupDimension)mgd).Attributes.Add(Project.Dimensions[cubeDim.ID].GetKeyAttribute());
                mga.Type = MeasureGroupAttributeType.Granularity;
                mga.KeyColumns.Add(measureGroup.GetTableName(), keycol);
            }
            else if (cubeDim.RefType == AdvantIQ.InstantCube.Model.CubeDimension.DimensionRefType.ManyToMany)
            {
                mgd = new ManyToManyMeasureGroupDimension(cubeDim.ID, cubeDim.BridgeMeasureGroup);
            }
            else if (cubeDim.RefType == AdvantIQ.InstantCube.Model.CubeDimension.DimensionRefType.Indirect)
            {
                mgd = new ReferenceMeasureGroupDimension();
                ((ReferenceMeasureGroupDimension)mgd).IntermediateCubeDimensionID = cubeDim.BridgeDimension;
            }
            else if (cubeDim.RefType == AdvantIQ.InstantCube.Model.CubeDimension.DimensionRefType.Degenerate)
            {
                mgd = new DegenerateMeasureGroupDimension(cubeDim.ID);
            }

            olapMeasureGroup.Dimensions.Add(mgd);
        }

        private void EmitHierarchy(Microsoft.AnalysisServices.Dimension olapDim, AdvantIQ.InstantCube.Model.Hierarchy hierarchy, AdvantIQ.InstantCube.Model.Dimension dimension)
        {
            if (!hierarchy.IsParentChildHierarchy)
            {
                var olapHierarchy = olapDim.Hierarchies.Add(hierarchy.Description);
                foreach (var hierarchyAttribute in hierarchy.Attributes)
                {
                    var olapLevel = olapHierarchy.Levels.Add(hierarchyAttribute);
                    olapLevel.SourceAttributeID = hierarchyAttribute;
                }
            }
            else
            {
                var olapAttribute = olapDim.Attributes.Add(hierarchy.Description, hierarchy.Description);
                olapAttribute.KeyColumns.Add(dimension.GetTableName(), hierarchy.GetParentKey());
                olapAttribute.RootMemberIf = RootIfValue.ParentIsBlankSelfOrMissing;
                olapAttribute.Usage = AttributeUsage.Parent;
                olapAttribute.MembersWithData = MembersWithData.NonLeafDataHidden;
            }
        }

        private void EmitAttribute(AdvantIQ.InstantCube.Model.Dimension dimension, 
            Microsoft.AnalysisServices.Dimension olapDim, AdvantIQ.InstantCube.Model.Attribute attribute)
        {
            var olapAttribute = olapDim.Attributes.Add(attribute.ID, attribute.ID);
            if (attribute.Key != null)
                olapAttribute.KeyColumns.Add(dimension.GetTableName(), attribute.Key.FieldName);
            if (attribute.Name != null)
                olapAttribute.NameColumn = new DataItem(dimension.GetTableName(), attribute.Name.FieldName);
            if (attribute.Value != null)
                olapAttribute.ValueColumn = new DataItem(dimension.GetTableName(), attribute.Value.FieldName);

            if (dimension is TimeDimension)
                olapAttribute.Type = GetTimeAttributeType(attribute.ID);
            olapAttribute.IsAggregatable = true;
            olapAttribute.OrderBy = OrderBy.Key;
            olapAttribute.AttributeHierarchyEnabled = true;
            olapAttribute.AttributeHierarchyVisible = attribute.Visible;
            if (dimension is TimeDimension && attribute.ID.ToLower() == "day")
                olapAttribute.Usage = AttributeUsage.Key;

            foreach (var attributeRelationship in attribute.RelatedRigidAttributes)
                olapAttribute.AttributeRelationships.Add(attributeRelationship).RelationshipType = RelationshipType.Rigid;
            
            foreach (var attributeRelationship in attribute.RelatedFlexibleAttributes)
                olapAttribute.AttributeRelationships.Add(attributeRelationship).RelationshipType = RelationshipType.Flexible;
        }

        private void EmitSurrogateKeyAttribute(AdvantIQ.InstantCube.Model.Dimension dimension, 
            Microsoft.AnalysisServices.Dimension olapDim)
        {
            var olapAttribute = olapDim.Attributes.Add(dimension.GetPrimaryKey(), dimension.GetPrimaryKey());
            olapAttribute.KeyColumns.Add(dimension.GetTableName(), dimension.GetPrimaryKey());

            if (dimension.GetNaturalKeyAttribute() != null && dimension.GetNaturalKeyAttribute().Name != null)
                olapAttribute.NameColumn = new DataItem(dimension.GetTableName(), dimension.GetNaturalKeyAttribute().Name.FieldName);

            olapAttribute.IsAggregatable = true;
            olapAttribute.OrderBy = OrderBy.Key;
            olapAttribute.AttributeHierarchyEnabled = true;
            olapAttribute.AttributeHierarchyVisible = false;
            olapAttribute.Usage = AttributeUsage.Key;
        }

        private AttributeType GetTimeAttributeType(string id)
        {
            switch (id.ToLower())
            {
                case "day": return AttributeType.Days;
                case "month": return AttributeType.Months;
                case "year": return AttributeType.Years;
                default: return AttributeType.Regular;
            }
        }

        private Type DetermineType(string sqlType)
        {
            if (sqlType.StartsWith("varchar") || sqlType.StartsWith("nvarchar"))
                return typeof(string);
            else if (sqlType.StartsWith("numeric"))
                return typeof(double);
            else if (sqlType.StartsWith("date"))
                return typeof(DateTime);
            else if (sqlType.StartsWith("int"))
                return typeof(Int32);
            else if (sqlType.StartsWith("bigint"))
                return typeof(Int64);
            else if (sqlType.StartsWith("smallint"))
                return typeof(Int16);
            else
                return typeof(object);
        }

        private System.Data.OleDb.OleDbType DetermineOleDbType(string sqlType)
        {
            if (sqlType.StartsWith("varchar") || sqlType.StartsWith("nvarchar"))
                return System.Data.OleDb.OleDbType.VarChar;
            else if (sqlType.StartsWith("numeric"))
                return System.Data.OleDb.OleDbType.Double;
            else if (sqlType.StartsWith("date"))
                return System.Data.OleDb.OleDbType.Date;
            else if (sqlType.StartsWith("int"))
                return System.Data.OleDb.OleDbType.Integer;
            else if (sqlType.StartsWith("bigint"))
                return System.Data.OleDb.OleDbType.BigInt;
            else if (sqlType.StartsWith("smallint"))
                return System.Data.OleDb.OleDbType.SmallInt;
            else if (sqlType.StartsWith("tinyint"))
                return System.Data.OleDb.OleDbType.TinyInt;
            else
                return System.Data.OleDb.OleDbType.IUnknown;
        }

        public void Dispose()
        {
            server.Disconnect();
        }
    }
}
